/**
 * User数据模型模块
 */

//导入SQLite3模块
const sqlite3 = require("sqlite3").verbose();
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");

class UserDB {
  /**
   * @constructor
   * @private
   */
  constructor() {
    this.dbFile = config.dbFile;
    this.instance = null;
    this.db = null;
    this.register = this.add;
  }
  /**
   * 创建UserDB对象
   * @returns {UserDB} UserDB实例
   */
  static getInstance() {
    if (!this.instance) {
      this.instance = new UserDB();
    }
    return this.instance;
  }
  /**
   * 连接数据库
   * @returns {Promise}
   */
  connect() {
    return new Promise((resolve, reject) => {
      this.db = new sqlite3.Database(this.dbFile, (err) => {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve("connect ok.");
        }
      });
    });
  }
  /**
   * 关闭数据库
   * @returns {Promise}
   */
  close() {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve("close ok.");
        }
      });
    });
  }

  /**
   * 获取指定ID的用户信息
   * @param {Number} userId 书籍ID
   * @returns {Promise}
   */
  find(userId) {
    return new Promise((resolve, reject) => {
      let sql = "SELECT * FROM users WHERE id=?";
      let params = [userId];
      this.db.get(sql, params, (err, result) => {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  }

  /**
   * 获取用户信息列表
   * @param {Number} limit 数量
   * @param {Number} offset 开始
   * @returns {Promise}
   */
  findAll(limit = -1, offset = -1) {
    return new Promise((resolve, reject) => {
      let sql = "";
      let params = [];
      if (limit === -1) {
        sql = "SELECT * FROM users ORDER BY id";
      } else if (offset === -1) {
        sql = "SELECT * FROM users ORDER BY id LIMIT ?";
        params[0] = limit;
      } else {
        sql = "SELECT * FROM users ORDER BY id LIMIT ? OFFSET ?";
        params[0] = limit;
        params[1] = offset;
      }
      this.db.all(sql, params, (err, result) => {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  }

  /**
   * 新增用户
   * @param {Object} user 用户数据
   * @returns {Promise}
   */
  add(user) {
    return new Promise((resolve, reject) => {
      let sql = `INSERT INTO users (
        username,password,nickname,truename,avatar,
        role,last_login_time,last_login_ip,created_time,created_ip,
        updated_time
      ) VALUES (
          ?,?,?,?,?,
          ?,?,?,?,?,
          ?
      );`;
      let params = [
        user.username,
        user.password,
        user.nickname,
        user.truename,
        user.avatar,
        user.role,
        user.lastLoginTime,
        user.lastLoginIP,
        user.createdTime,
        user.createdIP,
        user.updatedTime,
      ];
      this.db.run(sql, params, function (err, result) {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(this.lastID); //插入的数据的自增的ID
        }
      });
    });
  }

  /**
   * 修改用户
   * @param {Object} user 用户数据
   * @returns {Promise}
   */
  update(user) {
    return new Promise((resolve, reject) => {
      let sql = `UPDATE users SET 
              username=?,password=?,nickname=?,truename=?,avatar=?,role=?,
              updated_time =?
              WHERE id=?
          ;`;
      let params = [
        user.username,
        user.password,
        user.nickname,
        user.truename,
        user.avatar,
        user.role,
        user.updatedTime,
        user.id,
      ];
      this.db.run(sql, params, function (err, result) {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(this.changes); //影响的记录数
        }
      });
    });
  }

  /**
   * 删除用户
   * @param {Number} userId 用户ID
   * @returns {Promise}
   */
  remove(userId) {
    return new Promise((resolve, reject) => {
      let sql = "DELETE FROM users WHERE id=?";
      let params = [userId];
      this.db.run(sql, params, function (err, result) {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(this.changes); //影响的记录数
        }
      });
    });
  }

  /**
   * 获取用户数量
   * @returns   {Promise}
   */
  getCount() {
    return new Promise((resolve, reject) => {
    let sql = "SELECT count(1) AS total FROM users";
    let params = [];
    this.db.get(sql, params, function (err, result) {
    if (err) {
      util.err(err);
      reject(err);
    } else {
      resolve(result);
      }
      });
    });
    }

    /**
     * 搜索用户
     * @param {String} q 查询关键字
     * @param {Number} limit 数量
     * @param {Number} offset 偏移
     * @returns 
     */
    search(q,limit = -1, offset = -1) {
      return new Promise((resolve, reject) => {
       let sql = "";
       q=`%${q}%`;
       let params = [q, q, q];
        if (limit === -1) {
         sql = "SELECT * FROM users WHERE username LIKE ? OR nickname LIKE ? OR truename = ?";
        } else if (offset === -1) {
         sql = "SELECT * FROM users WHERE username LIKE ? OR nickname LIKE ? OR truename = ? LIMIT ?";
         params.push(limit);
        } else {
          sql = "SELECT * FROM users WHERE username LIKE ? OR nickname LIKE ? OR truename = ? LIMIT ? OFFSET ?";
          params.push(limit);
          params.push(offset);
        }
        this.db.all(sql, params, (err, result) => {
        if (err) {
              util.err(err);
              reject(err);
            } else {
              resolve(result);
            }
     });
      });
    };  

  /**
   * 获取指定ID的书籍信息
   * @param {String} username 用户名
   * @param {String} password 密码
   * @returns {Promise}
   */
  login(username, password) {
    return new Promise((resolve, reject) => {
    let sql = "SELECT * FROM users WHERE username = ? AND password = ?";
    let params = [username, password];
    this.db.get(sql, params, (err, result) => {
    if (err) {
      util.err(err);
      reject(err);
    } else {
      resolve(result);
    }
        });
      });
    }

    /**
   * 更新用户登录相关数据
   * @param {String} username 用户名
   * @param {Object} lastLogin 登录状态对象 
   * @returns {Promise}
   */
  touch(username, lastLogin) {
    return new Promise((resolve, reject) => {
    let sql = `UPDATE users SET 
                last_login_time = ? , last_login_ip = ?, 
                login_count = login_count + 1 
                WHERE username = ?`;
     let params = [lastLogin.time, lastLogin.ip, username];
     this.db.run(sql, params, (err, result) => {
     if (err) {
      util.err(err);
      reject(err);
     } else {
      resolve(this.changes);
     }
      });
    });
 }

 /**
   * 修改个人信息
   * @param {Object} id 用户id
   * @param {Object} info 用户信息
   * @returns {Promise}
   */
  changeInfo(id, info) {
    return new Promise((resolve, reject) => {
    let sql = `UPDATE users SET nickname = ?, truename = ?, avatar = ? WHERE id = ?;`;
    let params = [
      info.nickname,
      info.truename,
      info.avatar,
      id,
   ];
 
  this.db.run(sql, params, function (err, result) {
    if (err) {
      util.err(err);
      reject(err);
    } else {
      resolve(this.changes); //影响的记录数
    }
      });
    });
    }

  /**
   * 修改用户密码
   * @param {Object} id 用户id
   * @param {Object} password 用户密码
   * @returns {Promise}
   */
  changePassword(id, password) {
    return new Promise((resolve, reject) => {
    let sql = `UPDATE users SET password = ? WHERE id = ?;`;
    let params = [password,id];
    this.db.run(sql, params, function (err, result) {
    if (err) {
      util.err(err);
      reject(err);
    } else {
      resolve(this.changes); //影响的记录数
    }
      });
    });
    }

}



module.exports = UserDB;
